package CelestialDataAccess;

import com.sun.rowset.*;   //provides cached row set implementation
import javax.sql.rowset.*; //provides cached row set interface
import java.sql.*;         //provides connection object and sql exception
import java.text.SimpleDateFormat;
import java.util.Dictionary;
import java.util.List;

public class CustomersDataAccess extends DataAccessBase{

    private final static SimpleDateFormat sdf =
            new SimpleDateFormat("yyyy-mm-dd");

    /*************************************************************************/
    /* Client methods */
    public int insertCustomer(Customer cust){

        CallableStatement stm;

        try{
            stm = cnn.prepareCall("{CALL uspInsertCustomer(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
            stm.setBytes("Password", CryptService.encrypt(cust.getPassword()));
            stm.setInt("UserLevel", cust.getUserLevel());
            stm.setDate("RegisteredOn", java.sql.Date.valueOf(sdf.format(cust.getRegistrationDate())));

            stm.setString("FirstName", cust.getFirstName());
            stm.setString("LastName", cust.getLastName());
            stm.setString("Email", cust.getEmail());
            stm.setString("Contact", cust.getContact());
            stm.setDate("DOB", java.sql.Date.valueOf(sdf.format(cust.getDOB())));

            stm.setString("Country", cust.getCountry());
            stm.setString("City", cust.getCity());
            stm.setString("State", cust.getState());
            stm.setString("AddressLine1", cust.getAddressLine1());
            stm.setString("AddressLine2", cust.getAddressLine1());
            stm.setString("PostalCode", cust.getPostalCode());
            
            stm.setString("CreditCardNumber", cust.getCreditCardNumber());
            stm.setString("CreditCardCVV", cust.getCreditCardCVV());
            stm.setString("CreditCardHolder", cust.getCreditCardHolder());
            stm.setInt("CreditCardType", cust.getCreditCardType());
            stm.setDate("CreditCardExpiry", java.sql.Date.valueOf(sdf.format(cust.getCreditCardExpiry())));
            
            stm.setBoolean("IsActive", cust.isActive());

            stm.execute();
        }catch(Exception ex){
            return 0;
        }
        return 1;
    }
    /*----------------------------------------------------------------------*/
    public int updateCustomer(Customer cust){

        CallableStatement stm;

        try{
            stm = cnn.prepareCall("{CALL uspUpdateCustomer(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
            stm.setInt("CustomerID", cust.getCustomerID());
            stm.setBytes("Password", CryptService.encrypt(cust.getPassword()));
            stm.setInt("UserLevel", cust.getUserLevel());
            stm.setDate("RegisteredOn", java.sql.Date.valueOf(sdf.format(cust.getRegistrationDate())));

            stm.setString("FirstName", cust.getFirstName());
            stm.setString("LastName", cust.getLastName());
            stm.setString("Email", cust.getEmail());
            stm.setString("Contact", cust.getContact());
            stm.setDate("DOB", java.sql.Date.valueOf(sdf.format(cust.getDOB())));

            stm.setString("Country", cust.getCountry());
            stm.setString("City", cust.getCity());
            stm.setString("State", cust.getState());
            stm.setString("AddressLine1", cust.getAddressLine1());
            stm.setString("AddressLine2", cust.getAddressLine1());
            stm.setString("PostalCode", cust.getPostalCode());

            stm.setString("CreditCardNumber", cust.getCreditCardNumber());
            stm.setString("CreditCardCVV", cust.getCreditCardCVV());
            stm.setString("CreditCardHolder", cust.getCreditCardHolder());
            stm.setInt("CreditCardType", cust.getCreditCardType());
            stm.setDate("CreditCardExpiry", java.sql.Date.valueOf(sdf.format(cust.getCreditCardExpiry())));

            stm.setBoolean("IsActive", cust.isActive());

            stm.execute();
        }catch(Exception ex){
            return 0;
        }
         return 1;
    }
    /*----------------------------------------------------------------------*/
    public int deleteCustomer(Customer cust){

        CallableStatement stm;

        try{
            stm = cnn.prepareCall("{CALL uspDeleteCustomer(?)}");
            stm.setInt("CustomerID", cust.getCustomerID());

            stm.execute();
        }catch(SQLException ex){

            return 0;
        }
        return 1;
    }
    /*----------------------------------------------------------------------*/
    public static int getNextCustomerID(){

        CachedRowSet crs;
        ResultSet rs;
        CallableStatement stm;

        try{
            stm = cnn.prepareCall("{CALL uspGetNextCustomerID}");
            rs = stm.executeQuery();

            crs = new CachedRowSetImpl();
            crs.populate(rs);

            assert crs.last()==true;

            crs.last();
            return crs.getInt(1);
        }catch(SQLException ex){
            return -1;
        }
    }
    /*----------------------------------------------------------------------*/
    public void fill(Dictionary<Integer, Customer> dic) throws DataAccessException{

       try{
            CachedRowSet crs = getCustomers();
            while(crs.next()){
                dic.put(crs.getInt("CustomerID"), Customer.createCustomer(crs));
            }
       }catch (SQLException ex){
           throw new DataAccessException(ex.getMessage());
       }
    }
    /*----------------------------------------------------------------------*/
    public void fill(List<Customer> lst) throws DataAccessException{

         lst.clear();
         try{
            CachedRowSet crs = getCustomers();
            while(crs.next()){
                lst.add(Customer.createCustomer(crs));
            }
       }catch (SQLException ex){
           throw new DataAccessException(ex.getMessage());
       }
    }
    /*----------------------------------------------------------------------*/
    public Customer validateLogin(String email, String password) throws DataAccessException{

        CachedRowSet crs;
        ResultSet rs;
        CallableStatement stm;

        /* get customer records from database */
        try{
            crs = new CachedRowSetImpl();
            stm = cnn.prepareCall("{Call uspValidateCustomerLogin(?,?)}");
            stm.setString("Email", email);
            stm.setBytes("Password", CryptService.encrypt(password));

            rs = stm.executeQuery();
            crs.populate(rs);

            if(!crs.last()) return null;

        }catch(Exception ex){
            throw new DataAccessException(ex.getMessage());
        }
        /* try create new Customer object */
        return Customer.createCustomer(crs);
    }
    /*----------------------------------------------------------------------*/
    public boolean emailExists(String email) throws DataAccessException{
        CachedRowSet crs;
        ResultSet rs;
        CallableStatement stm;

        /* get customer records from database */
        try{
            crs = new CachedRowSetImpl();
            stm = cnn.prepareCall("{Call uspEmailExists(?)}");
            stm.setString("Email", email);

            rs = stm.executeQuery();
            crs.populate(rs);
            return crs.last();
            
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }   
    }
    /************************************************************************/
    /* Helpers */
    private CachedRowSet getCustomers() throws SQLException{

        CachedRowSet crs;
        ResultSet rs;
        CallableStatement stm;

        /* retrieve customer data from database */
        stm = cnn.prepareCall("{Call uspGetCustomers}");
        rs = stm.executeQuery();

        crs = new CachedRowSetImpl();
        crs.populate(rs);
  
        return crs;
    }
    /************************************************************************/
    /* test */
    public static void main(String[] args){
        
        Customer cust;
        CustomersDataAccess daCustomers;

        try{
            daCustomers = new CustomersDataAccess();

            DataAccessBase.open();

            /* test validate login */
            cust = daCustomers.validateLogin("chseng@homecodeworks.com", "12345678");
            System.out.println(cust.getEmail() + "\t"
                           + cust.getPassword() + "\t"
                           + cust.getFirstName() + "\t"
                           + cust.getCustomerID() + "\t"
                           + cust.getDOB().toString());
            
            /* test email exists */
            System.out.println(daCustomers.emailExists("chseng@homecodeworks.com"));

            /* test get next customer id */
            System.out.println(CustomersDataAccess.getNextCustomerID());
            /* test insert */
            /* test update */
            /* test delete */
            
            DataAccessBase.close();

        }catch(Exception ex){
            System.out.println(ex.getMessage());
            System.out.println(ex.toString());
            return;
        }
    }
    /*----------------------------------------------------------------------*/
}
